package com.lanou.dao;

import com.lanou.pojo.Book;
import com.lanou.pojo.Tb_user;
import com.lanou.utils.DBCPUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;

public class InsertDao {
//    Connection conn = DBCPUtil.getConnection();
//    QueryRunner qr = new QueryRunner();
    //添加用户(注册)
    public int insertUser(Tb_user tbUser) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int insert = qr.update(conn,"insert into tb_user (username,password,email,address,gender,phone,state) value(?,?,?,?,?,?,?)",
                tbUser.getUsername(),tbUser.getPassword(),tbUser.getEmail(),tbUser.getAddress(),
                tbUser.getGender(),tbUser.getPhone(),tbUser.getState());
        conn.commit();
        return insert;
    }
    //用username查找用户
    public Long selectUserByusername(Tb_user tbUser) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long count = qr.query(conn, "select count(*) from tb_user where username=?",
                new ScalarHandler<>(), tbUser.getUsername());
        return count;
    }
    //用cname查种类
    public Long selectCateByCname(String cname) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long catecount = qr.query(conn, "select count(*) from category where cname=?",
                new ScalarHandler<>(), cname);
        return catecount;
    }
    //添加种类
    public int insertCate(String cname) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int cate = qr.update(conn, "insert into category (cname) value (?)",
                cname);
        conn.commit();
        return cate;
    }
//  修改种类
    public int changeCate(int cid, String cname) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "update category set cname=? where cid=?",
                cname, cid);
        conn.commit();
        return update;
    }
    //删除种类
    public int delCate(Integer cid, String cname) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int delete = qr.update(conn, "delete from category where cid=? and cname=?",
                cid, cname);
        conn.commit();
        return delete;
    }
    //用bname查找书数量
    public Long selectBookBybname(String bname) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long books = qr.query(conn, "select count(*) from book where bname=?",
                new ScalarHandler<>(), bname);
        return books;
    }
    //用cname查找cid
    public int selectCateIDBycname(String cname) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int cid = qr.query(conn, "select cid from category where cname=?",
                new ScalarHandler<>(), cname);
        return cid;
    }
    //添加图书
    public int insertBook(String bname, String price, String author, String image, int cid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int insertbook = qr.update(conn, "insert into book (bname,price,author,image,cid) value (?,?,?,?,?)",
                bname, price, author, image, cid);
        conn.commit();
        return insertbook;
    }
    //用bid查找图书
    public Book selectBookBybid(int bid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Book book = qr.query(conn, "select * from book where bid=?",
                new BeanHandler<>(Book.class), bid);
        return book;
    }
    //用bid查找还有没有
    public Long selectBookcountBybid(int bid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long book = qr.query(conn, "select count(*) from book where bid=?",
                new ScalarHandler<>(), bid);
        return book;
    }
    //用cid查找cname
    public Object selectCateCnameBycid(int cid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Object cname = qr.query(conn, "select cname from category where cid=?",
                new ScalarHandler<>(), cid);
        return cname;
    }
    //删除图书
    public int delBook(int bid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int delbook = qr.update(conn, "delete from book where bid=?",
                bid);
        conn.commit();
        return delbook;
    }
    //修改图书
    public int changeBook(int bid, String bname, String price, String author, int cid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int book = qr.update(conn, "update book set bname=? ,price=?,author=?,cid=? where bid=?",
                bname, price, author, cid, bid);
        conn.commit();
        return book;
    }
    //通过bid和uid查找购物车
    public Long selectCarByBidAndUid(int uid, int bid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long carcount = qr.query(conn, "select count(*) from car where uid=? and bid=?",
                new ScalarHandler<>(), uid, bid);
        return carcount;
    }
    //添加新书进购物车
    public int insertCar(int uid, int bid, Book book) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "insert into car (uid,bid,count,total) value(?,?,?,?)",
                uid, bid, 1, book.getPrice());
        conn.commit();
        return update;
    }
//  购物车里已有书,加一本
    public int changeCar( int uid, int bid,int carbookcount,Book book) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        double total  = book.getPrice()*carbookcount;
        System.out.println(carbookcount);
        System.out.println(total);
        int update = qr.update(conn, "update car set count=? ,total=? where uid=? and bid=?",
                carbookcount, total, uid, bid);
        conn.commit();
        return update;
    }
//  查找购物车的书数量
    public int selectCarCountBybidAnduid(int bid, int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int carbookcount = qr.query(conn, "select count from car where uid=? and bid=?",
                new ScalarHandler<>(), uid, bid);
        return carbookcount;
    }
//通过购物车界面修改购物车
    public int changeCarByhtml(int uid, int bid, int count, double price) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        double total = count*price;
        int update = qr.update(conn, "update car set count=? ,total=? where uid=? and bid=?",
                count, total, uid, bid);
        conn.commit();
        return update;
    }
//删除单挑车
    public int delCarOne(int carid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "delete from car where carid=?",
                carid);
        conn.commit();
        return update;
    }
//创建订单
    public int insertOrders(String ordertime, double total, int uid, String address) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "insert into orders (ordertime,total,state,uid,address) value (?,?,?,?,?)",
                ordertime, total, 1, uid, address);
        if (update>0){
            String sql2="select @@identity";
            BigInteger b = qr.query(conn,sql2,new ScalarHandler<>());
            update = b.intValue();
        }
        conn.commit();
        return update;
    }
//创建订单详情
    public int insertOderitem(int count, double subtotal, int oid, int bid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "insert into orderitem (count,subtotal,oid,bid) value (?,?,?,?)",
                count, subtotal, oid, bid);
        conn.commit();
        return update;
    }
    //通过oid修改订单状态
    public int changeOrdersstateByoid(int oid, int newstate) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "update orders set state=? where oid=?",
                newstate, oid);
        conn.commit();
        return update;
    }
    //删除订单详情
    public int delorderitem(int oid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "delete from orderitem where oid=?",
                oid);
        conn.commit();
        return update;
    }
    //删除订单
    public int delorders(int oid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "delete from orders where oid=?", oid);
        conn.commit();
        return update;
    }
    //修改用户
    public int changeUser(int uid, String email, String address, String phone) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "update tb_user set email=? ,address=?,phone=? where uid=?",
                email, address, phone, uid);
        conn.commit();
        return update;
    }

    public int changeUserPassByuid(int uid, String newpassword) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        int update = qr.update(conn, "update tb_user set password=? where uid=?",
                newpassword, uid);
        conn.commit();
        return update;
    }
}
